package com.scuthnweb.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.FlushMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.scuthnweb.dao.Share_linkDao;
import com.scuthnweb.domain.Account;
import com.scuthnweb.domain.Share_link;
import com.scuthnweb.domain.transfer.ResLinkList;

/**
 * 
 * @author YJ.Huang
 *
 */
public class Share_linkDaoImpl extends HibernateDaoSupport implements Share_linkDao{

	@Override
	public void create(Share_link share_link) {
		this.getHibernateTemplate().save(share_link);
	}

	@Override
	public void delete(final Share_link share_link) {
		this.getHibernateTemplate().execute(
				new HibernateCallback() {
					@Override
					public Object doInHibernate(Session session) throws HibernateException, SQLException {
						session.setFlushMode(FlushMode.AUTO);
						session.beginTransaction();
						Query query = session.createSQLQuery("DELETE FROM share_link WHERE id=?")
								.setInteger(0, share_link.getId() );
						query.executeUpdate();					
						session.getTransaction().commit();
						return null; 
					}
				});	
	}

	@Override
	public void update(final Share_link share_link) {
		this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
						session.setFlushMode(FlushMode.AUTO); 
						session.update(share_link); 
						session.flush(); 
						return null; 
					}
		});		
	}

	@Override
	public Share_link get(Integer sid) {
		return (Share_link) this.getHibernateTemplate().get(Share_link.class, sid);
	}

	@Override
	public ResLinkList findByLink_nameAndCategoryAndPage(String link_name, String category,Integer page,Integer pageSize) {
		Integer start = page*pageSize;
		Integer end   = start+pageSize;
		String con_link_name = (link_name==null)||(link_name.equals(""))?null:" s.link_name LIKE ? ";
		String con_category = (category==null)||(category.equals(""))?null:" s.category=? ";
		String condition = "";
		Query query = null,query2 = null;		
		if(con_link_name!=null && con_category!=null){
			condition = " WHERE "+con_link_name+"AND"+con_category;
			String hql = "SELECT s.id,s.link_name,s.category,s.add_time,s.poster_account.id FROM Share_link s "+condition+" ORDER BY s.add_time DESC ";			
			query = this.getSession().createQuery(hql);
			query.setString(0, "%"+link_name+"%");
			query.setString(1, category);
			query.setFirstResult(start);
			query.setMaxResults(pageSize);
			hql = "SELECT COUNT(s.id) FROM Share_link s "+condition;
			query2 = this.getSession().createQuery(hql);
			query2.setString(0, "%"+link_name+"%");
			query2.setString(1, category);
		}
		else if(con_link_name==null && con_category!=null){
			condition = " WHERE "+con_category;
			String hql = "SELECT s.id,s.link_name,s.category,s.add_time,s.poster_account.id FROM Share_link s "+condition+" ORDER BY s.add_time DESC ";			
			query = this.getSession().createQuery(hql);
			query.setString(0, category);
			query.setFirstResult(start);
			query.setMaxResults(pageSize);
			hql = "SELECT COUNT(s.id) FROM Share_link s "+condition;
			query2 = this.getSession().createQuery(hql);
			query2.setString(0, category);
		}
		else if(con_link_name!=null && con_category==null){
			condition = " WHERE "+con_link_name;
			String hql = "SELECT s.id,s.link_name,s.category,s.add_time,s.poster_account.id FROM Share_link s "+condition+" ORDER BY s.add_time DESC ";			
			query = this.getSession().createQuery(hql);
			query.setString(0, "%"+link_name+"%");
			query.setFirstResult(start);
			query.setMaxResults(pageSize);
			hql = "SELECT COUNT(s.id) FROM Share_link s "+condition;
			query2 = this.getSession().createQuery(hql);
			query2.setString(0, "%"+link_name+"%");
		}
		else{
			condition = "";
			String hql = "SELECT s.id,s.link_name,s.category,s.add_time,s.poster_account.id FROM Share_link s "+condition+" ORDER BY s.add_time DESC ";			
			query = this.getSession().createQuery(hql);
			query.setFirstResult(start);
			query.setMaxResults(pageSize);
			hql = "SELECT COUNT(s.id) FROM Share_link s "+condition;
			query2 = this.getSession().createQuery(hql);
		}
		
		ResLinkList rls = new ResLinkList();
		rls.setReslink(this.makeResLinkList(query.list(),true));
		rls.setCount((Long)(query2.list().get(0)));
		return rls;
	}

	@Override
	public List<Object[]> findByUid(Integer uid) {
		String hql = "SELECT s.id,s.link_name,s.category,s.add_time FROM Share_link s WHERE s.poster_account.id=?";
		Query query = this.getSession().createQuery(hql);
		query.setInteger(0, uid);
		List ls = query.list();
		return ls;
	}

	@Override
	public ResLinkList findByUidAndPage(Integer uid, Integer page,Integer pageSize) {
		Integer start = page*pageSize;
		Integer end = start+pageSize;
		String hql = "SELECT s.id,s.link_name,s.category,s.add_time FROM Share_link s WHERE s.poster_account.id=? ";
		Query query = this.getSession().createQuery(hql);
		query.setInteger(0, uid);
		query.setFirstResult(start);
		query.setMaxResults(pageSize);
		List ls = query.list();
		
		hql = "SELECT COUNT(s.id) FROM Share_link s WHERE s.poster_account.id=? ";
		Query query1 = this.getSession().createQuery(hql);
		query1.setInteger(0, uid);
		List ls1 = query1.list();
		
		ResLinkList rls = new ResLinkList();
		rls.setReslink(this.makeResLinkList(ls,false));
		rls.setCount((Long)ls1.get(0));
		return rls;
	}
	
	private List makeResLinkList(List resLst,boolean y){
		Iterator<Object[]> itr = resLst.iterator();
		List ls = new ArrayList();
		while(itr.hasNext()){
		  Object[] temp = itr.next();
		  Share_link share_link = new Share_link();
		  share_link.setId((Integer)temp[0]);
		  share_link.setLink_name((String)temp[1]);
		  share_link.setCategory((String)temp[2]);
		  share_link.setAdd_time((java.sql.Timestamp)temp[3]);
		  if(y){
			  Account ac = new Account();
			  ac.setId((Integer)temp[4]);
			  share_link.setPoster_account(ac);
		  }
		  ls.add(share_link);
		}
		return ls;
	};
}
